package com.hnepri.fang.ftx.write;

import com.hnepri.fang.ftx.model.ResidentialDetail;
import com.hnepri.util.DBUtil;
import com.hnepri.util.FileUtil;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;

/**
 * 把文件数据写入数据库
 */
public class WriteResidentialDetail {
    static List<String> printFileds() {
        List<String> fileds = new ArrayList<>();
        String path = "E:\\小区\\zz\\mingxi";
        File f = new File(path);
        Set<String> keys = new HashSet<>();

        File [] fs = f.listFiles();
        for(File tmp: fs) {
            List<String> lines = FileUtil.getLineList(tmp.getAbsolutePath(), "utf-8");
            for(String line:lines) {
                if (line == null || line.trim().equals(""))continue;
                String field = line.substring(0, line.indexOf(":"));
                keys.add(field);
            }
        }
        for(String key: keys) {
            System.out.println(key);
        }
        return fileds;
    }
    static String parseResidentialDetail(File f) {
        StringBuilder sb = new StringBuilder();
        List<String> lines = FileUtil.getLineList(f.getAbsolutePath(), "utf-8");
        List<String> keys = ResidentialDetail.keys;
        Map<String,String> filedMap = new HashMap<>();

        for(String line:lines) {
            String key = line.substring(0, line.indexOf(":"));
            String v = line.substring(line.indexOf(":") + 1);
            filedMap.put(key, v);
        }
        for(String key: keys) {
            sb.append(filedMap.get(key) + "#");
        }
        if (sb.length() > 0) sb.deleteCharAt(sb.length() - 1);
        return sb.toString();
    }
    static List<String> getLines(String city) {
        List<String> ls = new ArrayList<>();
        String path = "E:\\小区\\" + city + "\\mingxi";
        File f = new File(path);
        File [] fs = f.listFiles();
        for(File tmp:fs) {
            ls.add(parseResidentialDetail(tmp));
        }
        return ls;
    }
    static void write2db(String city) {
        Connection con = null;
        PreparedStatement ps = null;
        List<String> lines = getLines(city);
        try {
            con = DBUtil.getConnection();
            con.setAutoCommit(false);
            String sql = "insert into xiaoqudetail(fid,projcode,x,y,xqdz,ssqy,yb,hxwz,cqms,wylb,jznd,kfs,jzlx,jzmj,zdmj,fwzs,ldzs,wygs,lhl,rjl,wyf,fjxx,gs,gn,gd,rq,txsb,dtfw,aqgl,wsfw,tcw,jzjg,wydh,wydd,xqrk,jtzk,zbxx)" +
                    "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            ps = con.prepareStatement(sql);

            int i = 0;
            for(String line:lines) {
                if (line == null || line.trim().equals(""))continue;
                i++;
                String [] datas = line.split("#");
                ps.setString(1, datas[0].trim());
                ps.setString(2, datas[1].trim());
                ps.setString(3, datas[2].trim());
                ps.setString(4, datas[3].trim());
                ps.setString(5, datas[4].trim());
                ps.setString(6, datas[5].trim());
                ps.setString(7, datas[6].trim());
                ps.setString(8, datas[7].trim());
                ps.setString(9, datas[8].trim());
                ps.setString(10, datas[9].trim());
                ps.setString(11, datas[10].trim());
                ps.setString(12, datas[11].trim());
                ps.setString(13, datas[12].trim());
                ps.setString(14, datas[13].trim());
                ps.setString(15, datas[14].trim());
                ps.setString(16, datas[15].trim());
                ps.setString(17, datas[16].trim());
                ps.setString(18, datas[17].trim());
                ps.setString(19, datas[18].trim());
                ps.setString(20, datas[19].trim());
                ps.setString(21, datas[20].trim());
                ps.setString(22, datas[21].trim());
                ps.setString(23, datas[22].trim());
                ps.setString(24, datas[23].trim());
                ps.setString(25, datas[24].trim());
                ps.setString(26, datas[25].trim());
                ps.setString(27, datas[26].trim());
                ps.setString(28, datas[27].trim());
                ps.setString(29, datas[28].trim());
                ps.setString(30, datas[29].trim());
                ps.setString(31, datas[30].trim());
                ps.setString(32, datas[31].trim());
                ps.setString(33, datas[32].trim());
                ps.setString(34, datas[33].trim());
                ps.setString(35, datas[34].trim());
                ps.setString(36, datas[35].trim());
                ps.setString(37, datas[36].trim());
                ps.addBatch();
                if (i % 100 == 0) {
                    ps.executeBatch();
                }
            }
            ps.executeBatch();

            con.commit();
        } catch (Exception e) {
            try {
                con.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            try {
                DBUtil.closeResource(con, ps);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
    public static void main(String[] args) {
        String city = "jiyuan";
        write2db(city);
        //printFileds();
        //System.out.println(getLines().size());
    }
}
